package cn.sstir.hackathon.core.poi;

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import cn.afterturn.easypoi.excel.entity.params.ExcelExportEntity;
import cn.sstir.hackathon.core.AbstractExportMap;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.DVConstraint;
import org.apache.poi.hssf.usermodel.HSSFDataValidation;
import org.apache.poi.ss.usermodel.Name;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.*;

/**
 * Excel导入导出工具类
 *
 * @author: fli
 * @email: fli@sstir.cn
 * @date: 2019/12/5 14:35
 */
public class ExcelUtil {

    private final static int START_ROW = 1;
    private final static int END_ROW = 100;

    public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName,
                                   boolean isCreateHeader, HttpServletResponse response) {
        ExportParams exportParams = new ExportParams(title, sheetName);
        exportParams.setCreateHeadRows(isCreateHeader);
        defaultExport(list, pojoClass, fileName, response, exportParams);
    }

    public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName,
                                   HttpServletResponse response) {
        defaultExport(list, pojoClass, fileName, response, new ExportParams(title, sheetName));
    }

    public static void exportExcel(List<Map<String, Object>> list, String fileName, HttpServletResponse response) {
        defaultExport(list, fileName, response);
    }




    /**
     * @author fli
     * @date 2020/1/16 13:14
     * @param clazz  字段映射抽象类
     * @param columns 字段名称集合
     * @param records 数据集合
     * @param fileName 文件名
     * @param title 表格名称
     * @param sheetName sheet名称
     */
    public static void exportExcel(Class<? extends AbstractExportMap> clazz, List<String> columns, HttpServletResponse response, List<Map<String, Object>> records, String fileName, String title, String sheetName) throws Exception{
        List<ExcelExportEntity> colList = new ArrayList<>();
        AbstractExportMap abstractExportMap = clazz.newInstance();
        for (String column : columns) {
            String fieldName = abstractExportMap.get(column);
            if(fieldName != null){
                ExcelExportEntity colEntity = new ExcelExportEntity(fieldName, column);
                colEntity.setNeedMerge(true);

                Map<String, String> collectionFieldMap = abstractExportMap.getCollectionFieldMap(column);
                if(collectionFieldMap != null){
                    List<ExcelExportEntity> colSubList = new ArrayList<>();
                    for (Map.Entry<String, String> entry : collectionFieldMap.entrySet()) {
                        colSubList.add(new ExcelExportEntity(entry.getValue(), entry.getKey()));
                    }
                    colEntity.setList(colSubList);
                }
                colList.add(colEntity);
            }
        }

        List<Map<String, Object>> list = new ArrayList<>();

        records.forEach(record -> {
            Map<String, Object> map = new HashMap<>(16);
            for (Map.Entry<String, Object> entry : record.entrySet()) {
                String key = entry.getKey();
                Object value = entry.getValue();
                map.put(key, value);
            }
            list.add(map);
        });
        Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams(title,sheetName), colList, list);
        downLoadExcel(fileName,response,workbook);
    }

    private static void defaultExport(List<?> list, Class<?> pojoClass, String fileName, HttpServletResponse response,
                                      ExportParams exportParams) {
        Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);
        downLoadExcel(fileName, response, workbook);
    }

    public static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) {
        try {
            response.setCharacterEncoding("UTF-8");
            response.setHeader("content-Type", "application/vnd.ms-excel");
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8")+".xls");
            workbook.write(response.getOutputStream());
        } catch (IOException e) {
            // throw new NormalException(e.getMessage());
        }
    }

    private static void defaultExport(List<Map<String, Object>> list, String fileName, HttpServletResponse response) {
        Workbook workbook = ExcelExportUtil.exportExcel(list, ExcelType.HSSF);
        if (workbook != null)
            ;
        downLoadExcel(fileName, response, workbook);
    }

    public static <T> List<T> importExcel(String filePath, Integer titleRows, Integer headerRows, Class<T> pojoClass) {
        if (StringUtils.isBlank(filePath)) {
            return null;
        }
        ImportParams params = new ImportParams();
        params.setTitleRows(titleRows);
        params.setHeadRows(headerRows);
        List<T> list = null;
        try {
            list = ExcelImportUtil.importExcel(new File(filePath), pojoClass, params);
        } catch (NoSuchElementException e) {
            // throw new NormalException("模板不能为空");
        } catch (Exception e) {
            e.printStackTrace();
            // throw new NormalException(e.getMessage());
        }
        return list;
    }

    public static <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows,
                                          Class<T> pojoClass) {
        if (file == null) {
            return null;
        }
        ImportParams params = new ImportParams();
        params.setTitleRows(titleRows);
        params.setHeadRows(headerRows);
        List<T> list = null;
        try {
            list = ExcelImportUtil.importExcel(file.getInputStream(), pojoClass, params);
        } catch (NoSuchElementException e) {
            // throw new NormalException("excel文件不能为空");
        } catch (Exception e) {
            // throw new NormalException(e.getMessage());
            System.out.println(e.getMessage());
        }
        return list;
    }


    /**
     * 生成excel下拉列表,防止单元格字符数超出255报错
     *
     * @author fli
     * @date 2019/12/19 11:00
     */
    public static void cellExport(Workbook workbook,int firstCol,int lastCol,String[] selects){
        String HIDDEN_SHEET = "hiddenSheet"+firstCol;
        Sheet sheet = workbook.getSheetAt(0);
        // 创建隐藏域
        Sheet hiddenSheet = workbook.createSheet(HIDDEN_SHEET);
        for (int i = 0; i < selects.length; i++) {
            // 循环赋值（为了防止下拉框的行数与隐藏域的行数相对应来获取>=选中行数的数组，将隐藏域加到结束行之后
            hiddenSheet.createRow(END_ROW + i).createCell(firstCol).setCellValue(selects[i]);
        }
        Name name = workbook.createName();
        name.setNameName(HIDDEN_SHEET);
        // A1:A代表隐藏域创建第?列createCell(?)时。以A1列开始A行数据获取下拉数组
        name.setRefersToFormula((HIDDEN_SHEET + "!A1:A" + (selects.length + END_ROW)));

        //  生成下拉列表
        //  只对(x，x)单元格有效
        CellRangeAddressList cellRangeAddressList = new CellRangeAddressList(START_ROW, END_ROW, firstCol, lastCol);
        //  生成下拉框内容
        DVConstraint dvConstraint = DVConstraint.createFormulaListConstraint(HIDDEN_SHEET);
        HSSFDataValidation dataValidation = new HSSFDataValidation(cellRangeAddressList, dvConstraint);
        // 其它隐藏、0显示
        int sheetIndex = workbook.getSheetIndex(hiddenSheet);
        workbook.setSheetHidden(sheetIndex,true);
        //  对sheet页生效
        sheet.addValidationData(dataValidation);

    }
}
